package com.gc.server.dao.dramaeval;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gc.client.util.GenreMapper;
import com.gc.exception.CantEvaluateException;
import com.gc.model.DramaModel;
import com.gc.server.manager.ConnectionManager;

public class DramaEvalInfoDaoImpl implements DramaEvalInfoDao {

	@Override
	public void evaluateDrama(Integer dramaId, String userId, String comment,
			Double point) throws CantEvaluateException {

		if (!alreadyEvaluate(dramaId, userId)) {
			if (insertEvaluateInfo(dramaId, point, userId, comment))
				return;

		} else {
			if (updateEvaluateInfo(dramaId, point, userId, comment))
				return;
		}

		throw new CantEvaluateException();
	}

	private boolean insertEvaluateInfo(Integer dramaId, Double point,
			String userId, String comment) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "INSERT INTO "
					+ "DRAMA_EVAL_INFO(DRAMA_ID, DRAMA_EVAL_INFO, EVALUATE_RATE, USER_COMMENT, USER_ID)"
					+ "values(?,SEQ_DRAMA_EVAL_ID.NEXTVAL ,?,?,?)";

			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, dramaId);
			pstmt.setDouble(2, point);
			pstmt.setString(3, comment);
			pstmt.setString(4, userId);
			int executeUpdate = pstmt.executeUpdate();
			if (executeUpdate == 1)
				return true;

			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}

		return false;
	}

	private boolean updateEvaluateInfo(Integer dramaId, Double point,
			String userId, String comment) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sqlUpdate = "UPDATE DRAMA_EVAL_INFO "
					+ "SET EVALUATE_RATE = ?, USER_COMMENT = ?"
					+ "WHERE DRAMA_ID = ? AND USER_ID = ?";

			PreparedStatement pstmt = connection.prepareStatement(sqlUpdate);
			pstmt.setDouble(1, point);
			pstmt.setString(2, comment);
			pstmt.setInt(3, dramaId);
			pstmt.setString(4, userId);

			int executeUpdate = pstmt.executeUpdate();
			connection.commit();
			if (executeUpdate == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}

		return false;
	}

	@Override
	public DramaModel getAvgRate(Integer dramaId) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "SELECT AVG(EVALUATE_RATE) " + "FROM DRAMA_EVAL_INFO "
					+ "WHERE DRAMA_ID = ? " + "GROUP BY DRAMA_ID";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, dramaId);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				double avgRate = rs.getDouble(1);
				DramaModel dramaModel = new DramaModel();
				dramaModel.setAvgEvaluatedPoint(avgRate);
				dramaModel.setDramaId(dramaId);
				return dramaModel;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return null;
	}

	private boolean alreadyEvaluate(Integer dramaId, String userId) {
		Connection connection = ConnectionManager.getConnection();
		try {
			String sql = "SELECT evaluate_rate FROM DRAMA_EVAL_INFO WHERE drama_id = ? AND user_id = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, dramaId);
			pstmt.setString(2, userId);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next())
				return true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return false;
	}

	@Override
	public List<DramaModel> getEvaluatedDrama(String userId) {
		Connection connection = ConnectionManager.getConnection();
		List<DramaModel> list = null;
		try {
			String sql = "SELECT D.DRAMA_ID, TITLE, GENRE, ACTOR, LAUNCH_DATE, TITLE_PIC, EXPLANATION, EVALUATE_RATE"
					+ " FROM DRAMA D, DRAMA_EVAL_INFO DEI "
					+ " WHERE D.DRAMA_ID = DEI.DRAMA_ID AND USER_ID = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userId);
			ResultSet rs = pstmt.executeQuery();

			list = new ArrayList<DramaModel>();

			while (rs.next()) {
				Integer dramaId = rs.getInt(1);
				String title = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
				java.util.Date launchDate = rs.getDate("LAUNCH_DATE");
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				Double myAvg = rs.getDouble("EVALUATE_RATE");
				DramaModel dm = new DramaModel(dramaId, title, genre, actor,
						launchDate, titlePic, explanation);
				dm.setMyAvg(myAvg);
				list.add(dm);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return list;
	}

	@Override
	public List<DramaModel> getRecommendedDrama(String userId) {
		List<DramaModel> evaluatedDrama = getEvaluatedDrama(userId);
		
		if(evaluatedDrama.size() == 0)
			return null;
		
		StringBuilder genreQuery = new StringBuilder();
		StringBuilder dramaQuery = new StringBuilder();
		
		for (int i = 0; i < evaluatedDrama.size(); i++) {
			String genre = evaluatedDrama.get(i).getGenre();
			Integer dramaId = evaluatedDrama.get(i).getDramaId();
			genreQuery.append("'"+genre+"'");
			dramaQuery.append("'"+dramaId+"'");
			
			if(i== evaluatedDrama.size()-1)
				break;
			genreQuery.append(",");
			dramaQuery.append(",");
		}

		Connection connection = ConnectionManager.getConnection();
		List<DramaModel> list = null;
		try {
			String sql = "SELECT D1.DRAMA_ID,LAUNCH_DATE, TITLE, GENRE, ACTOR, TITLE_PIC, EXPLANATION ,D2.AVG_RATE,"
					+ " (SELECT EVALUATE_RATE"
					+ " FROM DRAMA_EVAL_INFO"
					+ " WHERE DRAMA_ID = D1.DRAMA_ID AND USER_ID = ?) MYAVG"
					+ " FROM DRAMA D1, (SELECT DRAMA_ID , AVG(EVALUATE_RATE) AVG_RATE"
					+ " FROM DRAMA_EVAL_INFO DEI"
					+ " GROUP BY DRAMA_ID"
					+ " HAVING DRAMA_ID IN (SELECT DRAMA_ID"
					+ " FROM DRAMA"
					+ " WHERE GENRE IN("+ genreQuery +")"
					+ " AND DRAMA_ID NOT IN("+ dramaQuery +"))) D2"
					+ " WHERE D1.DRAMA_ID = D2.DRAMA_ID" 
					+ " AND AVG_RATE >= 0";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userId);
			ResultSet rs = pstmt.executeQuery();

			list = new ArrayList<DramaModel>();

			while (rs.next()) {
				Integer dramaId = rs.getInt(1);
				String title = rs.getString("TITLE");
				String genre = rs.getString("GENRE");
				String actor = rs.getString("ACTOR");
				java.util.Date launchDate = rs.getDate("LAUNCH_DATE");
				String titlePic = rs.getString("TITLE_PIC");
				String explanation = rs.getString("EXPLANATION");
				Double avgRate = rs.getDouble("AVG_RATE");
				DramaModel dm = new DramaModel(dramaId, title, genre, actor,
						launchDate, titlePic, explanation);
				dm.setAvgEvaluatedPoint(avgRate);
				list.add(dm);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.close(connection);
		}
		return list;
	}

}
